Cross Referencing Endowment Values

endowment_data <- read_rds(here("data", "endowment_filter_data_990.RDS")) 

companies_to_ein <- read_csv(here("data", "companies.csv")) %>%
  mutate(EIN = as.character(ein)) %>%
  select(-ein)
# make kable table with consistent formatting
make_table <- function(df, title = "", ...) {
  title <- paste0("<center><span style = 'font-size:160%;color:black'><b>",
                  title,
                  "</span></b><center>")
   as_tibble(df) %>%
    kbl(caption = title, ... ) %>%
    kable_material() %>%
    row_spec(row=0, background = "#43494C" , color = "white", bold = TRUE)
}

Notes on Strategy

We want to compare the current year variables CY to the current year minus X years variables labelled CYX. To do this, we can:

  • structure the data so each company has all available years (but all NAs for years where they had no data)
  • order by fiscal year
  • subtract the lagged CY variable from the CYX variable where the lag is X years. For example, for CYM1 we want to compare to the CY just one year ago, so lagged one year.

In this way, we obtain a collection of differences between reports that should be in concordance but are not always.

# plot missingness for a given variable 
# number of observations = number of observations 
# where that EIN had that variable (not NA)
plot_missing <- function(variable) {
  
  endowment_data %>%
    group_by(EIN) %>%
    # number of observations where variable is not NA 
    summarize(number_observations = sum(!is.na(!!sym(variable)))) %>%
    group_by(number_observations) %>%
    # number of EINs with each value of number_observations
    summarize(n_ein=n()) %>%
    ggplot(aes(x = number_observations, y =n_ein ))+
    geom_bar(stat="identity") +
    labs(y = "Number of Companies",
         x = paste0("Number of Observations where\n",
         variable, " was Not Missing"),
         title =paste0("Missingness for ", variable)) +
    theme_bw() +
    theme(plot.title = element_text(face = "bold", hjust = .5)) 

}




# compare values from CY to CYM* for given variable
# returns data frame that contains the difference between the CY value and 
# corresonding CM* values
# for example, the difference between the CY value for 2016 would be compared 
# to the CYM1 value for 2017, and the CYM2 value for 2018, and so on 
check_variable <- function(variable_name,
                           data) {
  
  
  base_name <- variable_name
  var <- paste0("CY", base_name)
  vars <- paste0("CYM", c( 1:4), base_name)
  
  # plt <- plot_missing(var)
  # print(plt)
  
  eins_with_variable <- data %>%
    group_by(EIN) %>%
    summarize(number_observations = sum(!is.na(!!sym(var)))) %>%
    filter(number_observations != 0) %>%
    pull(EIN)
  
  
  # the goal here is to create a row for each fiscal year, with NAs if 
  # there are no observations for that year
  # this is needed so that we have consecutive years, which is important
  # for substraction using lag() to work correctly

 data <- data %>%
  filter(EIN %in% eins_with_variable) %>%
  select(EIN, fiscal_year, contains(base_name)) %>%
   pivot_wider(names_from = fiscal_year, 
             #  names_prefix = "fiscalyear",
               values_from=contains(base_name)) %>%
   pivot_longer(cols = contains(base_name),
                names_to = "variable_year") %>%
   separate(variable_year, sep = "_", into = c("variable_name", "fiscal_year")) %>%
   pivot_wider(names_from = variable_name, values_from = value) %>%
   mutate(fiscal_year = as.factor(as.numeric(fiscal_year)))
 

  
  crossref <- data %>%
    group_by(EIN) %>%
    arrange(fiscal_year) %>%
    # lag corresponds to how far back the current year comparison should be
    # vars contains the CM* variables that represent reporting for years back
    # compare these CM* variables to the lagged current year (CY) variables
    mutate(
      difference_in_reported_year1 =  !!sym(vars[1]) - 
        lag(!!sym(var), n =1),
      difference_in_reported_year2 =  !!sym(vars[2]) - 
        lag(!!sym(var), n =2),
      difference_in_reported_year3 =  !!sym(vars[3]) - 
        lag(!!sym(var), n =3),
       difference_in_reported_year4 =  !!sym(vars[4]) - 
        lag(!!sym(var), n =4)
      )  %>%
    ungroup()

}

Cross Referencing Beginning Year Balance Amount

Comparison Across Years

As we might expect, we see that a higher proportion had a nonzero difference between the cross referenced reports for years further back in time. That is, reporting tended to be more accurate for most recent years.

crossref <- check_variable("BeginningYearBalanceAmt", data = endowment_data)
plot_missing("CYBeginningYearBalanceAmt")

# plot fraction where there was a difference between 
# the reports by year
crossref %>% 
  select(EIN, contains("difference")) %>%
  pivot_longer(cols = contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(name)  %>%
  summarize(number_zeros = sum(ifelse(value == 0, 1,0)),
            total_reports = n(),
            fraction = 1-( number_zeros / total_reports)) %>%
  mutate(name = gsub("difference_in_reported_year", "", name)) %>%
  ggplot(aes(x=name, y = fraction)) +
  geom_bar(stat ="identity", fill = "#234A77") +
  geom_label(aes(label = round(fraction,2))) +
  labs(title = paste0("Fraction of Differences that Were Nonzero\n",
                      "Between Cross Referenced Reports"),
       subtitle = "By Year",
       x = "Years Between Reports Compared",
       y = "Fraction with Nonzero Difference") +
  theme_bw() +
  theme(plot.title = element_text(hjust = .5, face="bold"),
        plot.subtitle = element_text(hjust = .5, face="italic"))

We also see we have fewer total comparisons of reports as we go back further back in time, because we can’t compute the 4 year comparison for any date where we don’t have a value 4 years back.

# stacked chart, note we can't see how nonzero counts are changing 
# relative to the total counts
crossref %>%
  select(EIN, contains("difference"), fiscal_year) %>%
  pivot_longer(cols = contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(name)  %>%
  summarize(zero = sum(ifelse(value == 0, 1,0)),
            nonzero = sum(ifelse(value == 0, 0,1))) %>%
  # notice each row represents a fiscal_year-EIN-difference_type 
  pivot_longer(cols = c(zero, nonzero),
               names_to = "source",
               values_to = "count") %>%
  mutate(name = gsub("difference_in_reported_year", "", name),
         source = ifelse(source == "nonzero",
                         "Nonzero Difference", 
                         "Zero Difference")) %>%
  ggplot(aes(x=name, y = count, fill = source)) +
  geom_bar(stat ="identity", position = "stack", alpha = .8) +
  geom_label(aes(label = round(count,3), y = count, color = source),
             position = "stack",
             size = 2.6,
             label.padding = unit(.1, "lines"),
             fill = "white",
             fontface="bold",
             show.legend = FALSE) +
  labs(title = "Number of Zero and Nonzero Differences\nBetween Cross Referenced Reports",
       subtitle = "By Year",
       x = "Years Between Reports Compared",
       y = "Count",
       fill = "") +
  theme_bw() +
  theme(plot.title = element_text(size = 16, hjust = .5, face="bold"),
        plot.subtitle = element_text(hjust = .5, face="italic"),
        axis.text.x = element_text(size = 13),
        axis.title = element_text(size = 16, face = "bold"))

Companies with Discordance in Reported Values

# difference represents What They Reported as CY Minus X Years - What They Reported at The Time

companies_different <- crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  filter(value > 0) %>%
   left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  arrange(organization_name) %>%
  pull(EIN) %>%
  unique()
  
crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  filter(value > 0) %>%
  left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  mutate(year = substr(name, nchar(name), nchar(name)),
         year = paste0("Comparing Current<br> Year Minus ",
                       year)) %>%
  arrange(organization_name) %>%
  select(`Organization Name` = organization_name,
         `Difference in Years` = year, 
         `Fiscal Year` = fiscal_year,
         `Recent  - Previously Reported` = value) %>%
  make_table(title = paste0(
    "Comparing Values Reported in More Recent Report to Those Previously Reported:<br>",
    "<i>Number of Companies that have at Least One Report Not Concordant: </i>",
    length(companies_different)),
             digits = 3, 
             format.args = list(
               big.mark = ",",
               scientific = FALSE),
    escape=FALSE,
    booktabs=TRUE)  %>%
  scroll_box(height = "450px",
             width = "100%") 
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 5
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Ballet Arizona Comparing Current
Year Minus 1
2018 4,025,025
Ballet Arizona Comparing Current
Year Minus 2
2018 500,000
Ballet Arizona Comparing Current
Year Minus 2
2019 4,025,025
Ballet Arizona Comparing Current
Year Minus 3
2019 500,000
Ballet Arizona Comparing Current
Year Minus 3
2020 4,025,025
Ballet Arizona Comparing Current
Year Minus 4
2020 500,000
Fort Wayne Ballet Comparing Current
Year Minus 1
2018 26,128
Fort Wayne Ballet Comparing Current
Year Minus 1
2019 13,343
Fort Wayne Ballet Comparing Current
Year Minus 2
2019 148,799
Fort Wayne Ballet Comparing Current
Year Minus 2
2020 13,343
Fort Wayne Ballet Comparing Current
Year Minus 3
2020 148,799
Pacific Northwest Ballet Comparing Current
Year Minus 1
2019 3,000
Pacific Northwest Ballet Comparing Current
Year Minus 2
2020 3,000
San Francisco Ballet Comparing Current
Year Minus 1
2017 107,033,401
San Francisco Ballet Comparing Current
Year Minus 2
2017 105,867,772
San Francisco Ballet Comparing Current
Year Minus 2
2018 107,033,401
San Francisco Ballet Comparing Current
Year Minus 3
2018 105,867,772
San Francisco Ballet Comparing Current
Year Minus 3
2019 107,033,401
San Francisco Ballet Comparing Current
Year Minus 4
2019 105,867,772
San Francisco Ballet Comparing Current
Year Minus 4
2020 107,033,401
The Alabama Ballet Comparing Current
Year Minus 1
2019 227,040
The Alabama Ballet Comparing Current
Year Minus 2
2020 227,040
The Alabama Ballet Comparing Current
Year Minus 3
2020 219,787
The Alabama Ballet Comparing Current
Year Minus 4
2020 254,152

We see that values are repeated because if there is some value that is quite off, say for 2016, then this shows up in the CYM1 for 2017, but also CYM2 for 2018, CYM3 for 2019 and so on.

Tables of Reported Values for Each Company with Discordance in Reported Values

Interpretation:

  • The easiest way to interpret the company-specific tables is to look diagonally left-to right. For example, 2018 CY should match 2019 CYM1, 2020 CYM2, and 2021 CYM3 (though the 2021 values often are NA at this time).

Observations:

  • We see in some cases, the problematic reports are clear initially. This is the case in San Francisco Ballet, Ballet Arizona, or the Alabama Ballet.
  • The differences for Fort Wayne Ballet and the Pacific Northwest Ballet are more subtle.
# iterate through EINs where there was discordance and
# generate a table so we can better see what's going on

variable_name <- "BeginningYearBalanceAmt"

walk(1:length(companies_different), ~{
  name <- companies_to_ein %>%
    filter(EIN == companies_different[.x]) %>%
    pull(organization_name)
  
  table <- crossref %>% 
    rename_with(cols=everything(), ~gsub(variable_name, "", .)) %>%
    filter(EIN %in% companies_different[.x]) %>%
    select(-c(EIN, contains("difference"))) %>%
    make_table(title = paste0("Reports for ",
                              name, "<br>EIN: ", 
                              companies_different[.x],
                               ", Variable: ", variable_name))
  
  print(table)
  
#  print(table)

})
Reports for Ballet Arizona
EIN: 860367773, Variable: BeginningYearBalanceAmt
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 101399 100399 100399 100399 100399
2016 101399 101399 100399 100399 100399
2017 101399 101399 101399 100399 100399
2018 4746867 4126424 601399 NA NA
2019 4732822 4746867 4126424 601399 NA
2020 4670922 4732822 4746867 4126424 601399
2021 NA NA NA NA NA
Reports for Fort Wayne Ballet
EIN: 356006394, Variable: BeginningYearBalanceAmt
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 NA NA NA NA NA
2016 NA NA NA NA NA
2017 1264981 1191922 1174150 36538 36538
2018 1413780 1291109 1219104 1201082 60137
2019 1415612 1427123 1413780 1291109 1219104
2020 1422619 1415612 1427123 1413780 1291109
2021 NA NA NA NA NA
Reports for Pacific Northwest Ballet
EIN: 910897129, Variable: BeginningYearBalanceAmt
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 16919887 15702692 14297377 14671226 12528462
2016 17026097 16919887 15702692 14297377 14671226
2017 15778987 17026097 16919887 15702692 14297377
2018 18145008 15778987 17026097 16919887 15702692
2019 19254508 18148008 15778987 17026097 16919887
2020 19741644 19254508 18148008 15778987 17026097
2021 NA NA NA NA NA
Reports for San Francisco Ballet
EIN: 941415298, Variable: BeginningYearBalanceAmt
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 174 174 174 1035814 2318646
2016 174 174 174 174 1035814
2017 100219009 107033575 105867946 92513161 79137681
2018 119047942 100219009 107033575 105867946 92513161
2019 125015507 119047942 100219009 107033575 105867946
2020 124452193 125015507 119047942 100219009 107033575
2021 NA NA NA NA NA
Reports for The Alabama Ballet
EIN: 630813626, Variable: BeginningYearBalanceAmt
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 NA NA NA NA NA
2016 250000 250000 250000 250000 250000
2017 250000 250000 250000 250000 250000
2018 250000 250000 250000 250000 250000
2019 446591 477040 250000 250000 250000
2020 430113 446591 477040 469787 504152
2021 NA NA NA NA NA
crossref %>%
  pivot_longer(cols = contains("difference")) %>%
  select(EIN, fiscal_year, name, value) %>%
  # filter(value > 0) %>%
  left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
  mutate(year = substr(name, nchar(name), nchar(name)),
         year = paste0("Comparing Current Year Minus ",
                       year)) %>%
  arrange(organization_name) %>% View()

Cross Referencing All Endowment Variables

Missingness by Variable

variables_to_check  <- endowment_data %>%
  select(contains("CY")) %>%
  colnames() %>%
  gsub("CY|CYM.", "",.) %>%
  unique()

crossref_all <- map_df(
  variables_to_check,
  ~{  variable_name <- .x
  check_variable(variable_name,
                 data = endowment_data) %>% 
    # remove variable name part of column name 
    # so we can bind rows together, add this information
    # as a separate column
    rename_with(cols=everything(), 
                ~gsub(variable_name, "", .)) %>%
    mutate(variable = .x)
})


missing_all <- map_df( variables_to_check, 
 ~ {variable <- paste0("CY",.x)
    endowment_data %>%
      group_by(EIN) %>%
      summarize(number_observations = sum(!is.na(!!sym(variable)))) %>%
      group_by(number_observations) %>%
      summarize(number_eins=n()) %>%
      mutate(variable = variable)
})
colors <- c("#58b5e1", "#49406e", "#9dd84e", "#6633b4", "#46ebdc")


missing_all %>%
  mutate(number_observations = paste0(
    "Number of EINS with ",
    number_observations,
    " Observations for this Variable" )) %>%
      ggplot(aes(x = variable, y =number_eins, fill = variable))+
      geom_bar(stat="identity",
               position = "dodge",
               show.legend=FALSE) +
      geom_label(aes(label = number_eins,
                     color = variable),
                 fill = "white",
                 vjust = .5,
                 size = 2,
                 position = position_dodge(1),
                 label.padding = unit(.1, "lines"),
                 show.legend=FALSE) +
       facet_wrap(~number_observations, ncol=1) +
  coord_flip() +
      labs(y = "Number of Companies",
           x = "Variable Name",
           title = "Comparing Missingness Across Variables") +
      theme_bw() +
      theme(plot.title = element_text(face = "bold", hjust = .5),
            axis.title = element_text(face = "bold")) +
  scale_fill_manual(values = colors) +
  scale_color_manual(values = colors) +
  scale_y_continuous(n.breaks = 8) 

Fraction Discordant by Variable

# plot fraction discordant for each variable
crossref_all %>%
  select(EIN, contains("difference"), variable) %>%
  pivot_longer( contains("difference")) %>%
  filter(!is.na(value)) %>%
  group_by(variable) %>%
  summarize(
    number_of_discordant_observations = sum(value > 1),
    total_observations_of_variable = n(),
    fraction_discordant = number_of_discordant_observations / total_observations_of_variable) %>%
  ggplot(aes(x = fct_reorder(variable,
                             fraction_discordant,
                             .desc = TRUE),
             y = fraction_discordant)) +
  geom_bar(stat="identity",
           fill = "#234A77")+
  geom_label(aes(label = round(fraction_discordant, 3))) +
  theme_bw() +
  theme(plot.title = element_text(face = "bold", hjust = .5, size = 16),
            axis.title = element_text(face = "bold", size =16),
            axis.text.x = element_text(size = 12, angle = 10, vjust = .6)) +
  labs(y = "Fraction Discordant",
       x = "Endowment Variable",
       title = "Fraction of Observations that Were Discordant for Each Variable")

# generate table displaying the discordant values for a given variable
get_discordant_table <- function(variable_name, data) {
    
    # observations with nonzero difference 
    cross_ref_for_var <- data %>%
      filter(variable == variable_name) %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value) %>%
      filter(value > 0) 
    
    # EINs that have at least one discordance 
    discordant <- cross_ref_for_var %>%
      pull(EIN) %>% unique()
    
  # generate table displaying discordances
  cross_ref_for_var %>%
    left_join(companies_to_ein, by = c("EIN" = "EIN")) %>%
    mutate(year = substr(name, nchar(name), nchar(name)),
           year = paste0("Comparing Current<br> Year Minus ",
                         year)) %>%
    arrange(organization_name) %>%
    select(`Organization Name` = organization_name,
           `Difference in Years` = year, 
           `Fiscal Year` = fiscal_year,
           `Recent  - Previously Reported` = value) %>%
    make_table(title = paste0("Variable: ", 
                              variable_name,
      "<br>Comparing Values Reported in More Recent Report to Those Previously Reported:<br>",
      "<i>Number of Companies that have at Least One Report Not Concordant: </i>",
      length(discordant)),
               digits = 3, 
               format.args = list(
                 big.mark = ",",
                 scientific = FALSE),
      escape=FALSE,
      booktabs=TRUE)  %>%
    scroll_box(height = "450px",
               width = "100%") 

}

# iterate over all variables to check and generate table
walk(variables_to_check, ~{
  table_for_var <- get_discordant_table(.x, data = crossref_all)
  print(table_for_var)
})
Variable: BeginningYearBalanceAmt
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 5
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Ballet Arizona Comparing Current
Year Minus 1
2018 4,025,025
Ballet Arizona Comparing Current
Year Minus 2
2018 500,000
Ballet Arizona Comparing Current
Year Minus 2
2019 4,025,025
Ballet Arizona Comparing Current
Year Minus 3
2019 500,000
Ballet Arizona Comparing Current
Year Minus 3
2020 4,025,025
Ballet Arizona Comparing Current
Year Minus 4
2020 500,000
Fort Wayne Ballet Comparing Current
Year Minus 1
2018 26,128
Fort Wayne Ballet Comparing Current
Year Minus 1
2019 13,343
Fort Wayne Ballet Comparing Current
Year Minus 2
2019 148,799
Fort Wayne Ballet Comparing Current
Year Minus 2
2020 13,343
Fort Wayne Ballet Comparing Current
Year Minus 3
2020 148,799
Pacific Northwest Ballet Comparing Current
Year Minus 1
2019 3,000
Pacific Northwest Ballet Comparing Current
Year Minus 2
2020 3,000
San Francisco Ballet Comparing Current
Year Minus 1
2017 107,033,401
San Francisco Ballet Comparing Current
Year Minus 2
2017 105,867,772
San Francisco Ballet Comparing Current
Year Minus 2
2018 107,033,401
San Francisco Ballet Comparing Current
Year Minus 3
2018 105,867,772
San Francisco Ballet Comparing Current
Year Minus 3
2019 107,033,401
San Francisco Ballet Comparing Current
Year Minus 4
2019 105,867,772
San Francisco Ballet Comparing Current
Year Minus 4
2020 107,033,401
The Alabama Ballet Comparing Current
Year Minus 1
2019 227,040
The Alabama Ballet Comparing Current
Year Minus 2
2020 227,040
The Alabama Ballet Comparing Current
Year Minus 3
2020 219,787
The Alabama Ballet Comparing Current
Year Minus 4
2020 254,152
Variable: ContributionsAmt
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 2
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Joffrey Ballet Comparing Current
Year Minus 1
2017 41,702
Joffrey Ballet Comparing Current
Year Minus 2
2018 41,702
Joffrey Ballet Comparing Current
Year Minus 3
2019 41,702
Joffrey Ballet Comparing Current
Year Minus 4
2020 41,702
San Francisco Ballet Comparing Current
Year Minus 1
2017 3,440,416
San Francisco Ballet Comparing Current
Year Minus 2
2017 6,457,496
San Francisco Ballet Comparing Current
Year Minus 2
2018 3,440,416
San Francisco Ballet Comparing Current
Year Minus 3
2018 6,457,496
San Francisco Ballet Comparing Current
Year Minus 1
2019 1,025,273
San Francisco Ballet Comparing Current
Year Minus 2
2019 2,479,840
San Francisco Ballet Comparing Current
Year Minus 3
2019 3,440,416
San Francisco Ballet Comparing Current
Year Minus 4
2019 6,457,496
San Francisco Ballet Comparing Current
Year Minus 2
2020 1,025,273
San Francisco Ballet Comparing Current
Year Minus 3
2020 2,479,840
San Francisco Ballet Comparing Current
Year Minus 4
2020 3,440,416
Variable: InvestmentEarningsOrLossesAmt
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 4
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Fort Wayne Ballet Comparing Current
Year Minus 1
2018 2,568
Pittsburgh Ballet Theatre Comparing Current
Year Minus 1
2019 80,765
Pittsburgh Ballet Theatre Comparing Current
Year Minus 2
2020 80,765
Pittsburgh Ballet Theatre Comparing Current
Year Minus 1
2021 25,082
Pittsburgh Ballet Theatre Comparing Current
Year Minus 3
2021 80,765
San Francisco Ballet Comparing Current
Year Minus 2
2017 1,133,639
San Francisco Ballet Comparing Current
Year Minus 3
2018 1,133,639
San Francisco Ballet Comparing Current
Year Minus 4
2019 1,133,639
The Alabama Ballet Comparing Current
Year Minus 3
2020 30,742
Variable: OtherExpendituresAmt
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 4
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Fort Wayne Ballet Comparing Current
Year Minus 1
2019 3,149
Fort Wayne Ballet Comparing Current
Year Minus 2
2020 3,149
Pittsburgh Ballet Theatre Comparing Current
Year Minus 1
2020 300
Pittsburgh Ballet Theatre Comparing Current
Year Minus 2
2021 300
San Francisco Ballet Comparing Current
Year Minus 1
2017 5,795,836
San Francisco Ballet Comparing Current
Year Minus 2
2017 5,498,836
San Francisco Ballet Comparing Current
Year Minus 2
2018 5,795,836
San Francisco Ballet Comparing Current
Year Minus 3
2018 5,498,836
San Francisco Ballet Comparing Current
Year Minus 3
2019 5,795,836
San Francisco Ballet Comparing Current
Year Minus 4
2019 5,498,836
San Francisco Ballet Comparing Current
Year Minus 4
2020 5,795,836
The Alabama Ballet Comparing Current
Year Minus 3
2020 23,489
The Alabama Ballet Comparing Current
Year Minus 4
2020 25,201
Variable: EndYearBalanceAmt
Comparing Values Reported in More Recent Report to Those Previously Reported:
Number of Companies that have at Least One Report Not Concordant: 8
Organization Name Difference in Years Fiscal Year Recent - Previously Reported
Ballet Arizona Comparing Current
Year Minus 1
2018 973,030
Ballet Arizona Comparing Current
Year Minus 2
2018 4,025,025
Ballet Arizona Comparing Current
Year Minus 3
2018 500,000
Ballet Arizona Comparing Current
Year Minus 2
2019 973,030
Ballet Arizona Comparing Current
Year Minus 3
2019 4,025,025
Ballet Arizona Comparing Current
Year Minus 4
2019 500,000
Ballet Arizona Comparing Current
Year Minus 3
2020 973,030
Ballet Arizona Comparing Current
Year Minus 4
2020 4,025,025
Fort Wayne Ballet Comparing Current
Year Minus 1
2018 38,699
Fort Wayne Ballet Comparing Current
Year Minus 2
2019 52,042
Fort Wayne Ballet Comparing Current
Year Minus 3
2020 52,042
Joffrey Ballet Comparing Current
Year Minus 1
2017 41,702
Joffrey Ballet Comparing Current
Year Minus 2
2018 41,702
Joffrey Ballet Comparing Current
Year Minus 3
2019 41,702
Joffrey Ballet Comparing Current
Year Minus 4
2020 41,702
Pacific Northwest Ballet Comparing Current
Year Minus 1
2019 3,000
Pacific Northwest Ballet Comparing Current
Year Minus 2
2020 3,000
Pittsburgh Ballet Theatre Comparing Current
Year Minus 1
2019 80,765
Pittsburgh Ballet Theatre Comparing Current
Year Minus 2
2020 80,765
Pittsburgh Ballet Theatre Comparing Current
Year Minus 1
2021 25,082
Pittsburgh Ballet Theatre Comparing Current
Year Minus 3
2021 80,765
San Francisco Ballet Comparing Current
Year Minus 1
2017 100,218,835
San Francisco Ballet Comparing Current
Year Minus 2
2017 107,033,401
San Francisco Ballet Comparing Current
Year Minus 2
2018 100,218,835
San Francisco Ballet Comparing Current
Year Minus 3
2018 107,033,401
San Francisco Ballet Comparing Current
Year Minus 3
2019 100,218,835
San Francisco Ballet Comparing Current
Year Minus 4
2019 107,033,401
San Francisco Ballet Comparing Current
Year Minus 4
2020 100,218,835
Texas Ballet Theater Comparing Current
Year Minus 1
2016 143
Texas Ballet Theater Comparing Current
Year Minus 2
2017 143
Texas Ballet Theater Comparing Current
Year Minus 3
2018 143
Texas Ballet Theater Comparing Current
Year Minus 4
2019 143
The Alabama Ballet Comparing Current
Year Minus 1
2019 196,591
The Alabama Ballet Comparing Current
Year Minus 2
2020 196,591
The Alabama Ballet Comparing Current
Year Minus 3
2020 227,040
The Alabama Ballet Comparing Current
Year Minus 4
2020 219,787

Companies with Discordant Reporting for at Least One Variable

# variables corresponding to number of companies with at least one discordance
crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
      group_by(EIN) %>%
      summarize(
                number_variables = length(unique(variable)),
                variable = paste(unique(variable), collapse=",<br>")) %>%
  left_join(companies_to_ein) %>%
  arrange(organization_name) %>%
  select(`Organization Name` = `organization_name`,
         `Number of Variables Discordant` = number_variables,
         `Variables with Discordant Reporting` = variable) %>%
  make_table(
    title = "Companies with Discordant Reporting for at Least One Variable",
    escape=FALSE)
Companies with Discordant Reporting for at Least One Variable
Organization Name Number of Variables Discordant Variables with Discordant Reporting
Ballet Arizona 2 BeginningYearBalanceAmt,
EndYearBalanceAmt
Fort Wayne Ballet 4 BeginningYearBalanceAmt,
InvestmentEarningsOrLossesAmt,
OtherExpendituresAmt,
EndYearBalanceAmt
Joffrey Ballet 2 ContributionsAmt,
EndYearBalanceAmt
Pacific Northwest Ballet 2 BeginningYearBalanceAmt,
EndYearBalanceAmt
Pittsburgh Ballet Theatre 3 InvestmentEarningsOrLossesAmt,
OtherExpendituresAmt,
EndYearBalanceAmt
San Francisco Ballet 5 BeginningYearBalanceAmt,
ContributionsAmt,
InvestmentEarningsOrLossesAmt,
OtherExpendituresAmt,
EndYearBalanceAmt
Texas Ballet Theater 1 EndYearBalanceAmt
The Alabama Ballet 4 BeginningYearBalanceAmt,
InvestmentEarningsOrLossesAmt,
OtherExpendituresAmt,
EndYearBalanceAmt
# for each variable, list of EINs that have at least one discordance 
intersections <- crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
      group_by(variable) %>%
      summarize(EINs = list(unique(EIN)))

discord_in_all <- Reduce(intersect, intersections$EINs) %>% unique() %>% length() 
discord_at_least_one <- Reduce(union, intersections$EINs) %>% unique() %>% length() 

The number of companies with a discordant report for all variables was 1, and the number of companies with at least one discordant report for all variables was 8.

# visualize discordances in given variable_name 
plot_reported_for_variable <- function(variable_name, crossref, endowment) {
  
  cross_ref_for_var <- crossref %>%
      filter(variable == variable_name) %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value) %>%
      filter(value > 0) 
    
  discordant <- cross_ref_for_var %>%
      pull(EIN) %>% unique()
  
  number_cols <- ifelse(length(discordant) <= 6, 1,2)
  
  
  # plot the values for the year they correspond to so we can compare,
  # for example, if CM1 for 2016 is the same as CY for 2015
  endowment %>%
    filter(EIN %in% discordant) %>%
    select(EIN, fiscal_year, contains(variable_name)) %>%
    group_by(EIN) %>%
    arrange(fiscal_year) %>% 
    pivot_longer(3: ncol(.)) %>%
    mutate(source = ifelse(grepl("CYM", name), substr(name, 1,4), "CY"),
           year_lag = ifelse(grepl("CYM", name), substr(source, 4,4), 0),
           year_lag = as.numeric(year_lag),
           fiscal_year = as.integer(paste0(fiscal_year))) %>%
    mutate(value_year = fiscal_year -year_lag
           ) %>%
    left_join(companies_to_ein) %>%
    mutate(organization_name = paste0(organization_name, 
                                      " (EIN: ", EIN, ")")) %>%
    ggplot(aes(x = value_year, y = value)) +
    geom_jitter(aes(fill=source), height  =0, 
                width = .2,
                alpha = .8,
                size = 2.2,
                shape =21,
                color = "black",
                stroke =.4) +
   # geom_line(aes(group = source, color = source)) +
    facet_wrap(~organization_name, scales= 'free_y', ncol = number_cols) +
    scale_x_continuous(breaks = 2011:2021 ) +
    scale_y_continuous(labels = comma) +
    viridis::scale_fill_viridis(option="magma", discrete=TRUE) +
    theme_bw() +
    labs(x = "Fiscal Year",
         y = "Reported Value (Dollars)",
         title = paste0("Comparing Reported Values for ", variable_name),
         subtitle = "Only Considering Companies with at Least One Discordant Value") +
    theme(plot.title = element_text(
      face = "bold",
      hjust = .5, 
      size = 16),
      axis.title = element_text(face = "bold", size =16),
      axis.text = element_text(size = 12),
      strip.text = element_text(face = "bold", size = 14),
      plot.subtitle=element_text(size =14, 
                                 face="italic",
                                 hjust = .5),
      legend.text = element_text(size = 10),
      legend.title = element_text(face = "bold", size = 12)) +
    guides(legend = guide_legend(override.aes = list(size = 3)))

}

# plot variables by year, by variable only for EINs that have
# at least one discordance for a given variable
walk(unique(variables_to_check),
     ~ {plt <- plot_reported_for_variable(
       variable_name = .x,
       crossref = crossref_all,
       endowment = endowment_data)
     print(plt) })

Questions to Consider

  • Should we assume the most recently reported values are (the most) accurate?

Further Analysis into the Discrepancies

Checking Whether Any Were Amended

None of the filings with discrepancies were amended filings.

# get amended index from form 990 
form_990 <- read_rds(here("data", "data_990.RDS"))

# check if any observations with discrepancies were from amended filing
crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
  left_join(form_990) %>%
  filter(!is.na(AmendedReturnInd))

San Francisco Ballet

Tables for Each Variable with Discordance

companies_different_any_variable <- intersections$EINs %>% 
  unlist() %>% 
  unique() %>%
  tibble(EIN = .) %>%
  left_join(companies_to_ein)


# variables discordant by EIN
vars_disc <- crossref_all %>%
      pivot_longer(cols = contains("difference")) %>%
      select(EIN, fiscal_year, name, value, variable) %>%
      filter(value > 0) %>%
      group_by(EIN) %>%
      summarize(variables = list(unique(variable)))



ein <- companies_different_any_variable %>%
  filter(organization_name == "San Francisco Ballet") %>%
  pull(EIN)
  
vars_to_check <- vars_disc %>% 
  filter(EIN == ein) %>%
  pull(variables) %>%
  unlist()


walk(vars_to_check, ~ {
   name <- companies_to_ein %>%
    filter(EIN%in%ein) %>%
    pull(organization_name)
  
  table <- crossref_all %>%
    filter(variable == .x & EIN == ein) %>%
    select(-c(EIN, contains("difference"), variable)) %>%
    make_table(title = paste0("Reports for ",
                              name,
                               "<br>Variable: ", .x,  
                              "<br>EIN: ", 
                              ein))
  
  print(table)
  
})
Reports for San Francisco Ballet
Variable: BeginningYearBalanceAmt
EIN: 941415298
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 174 174 174 1035814 2318646
2016 174 174 174 174 1035814
2017 100219009 107033575 105867946 92513161 79137681
2018 119047942 100219009 107033575 105867946 92513161
2019 125015507 119047942 100219009 107033575 105867946
2020 124452193 125015507 119047942 100219009 107033575
2021 NA NA NA NA NA
Reports for San Francisco Ballet
Variable: ContributionsAmt
EIN: 941415298
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 11275 686250 0 0 652262
2016 0 11275 686250 0 0
2017 11604394 3440416 6468771 5968410 4316905
2018 6675957 11604394 3440416 6468771 5968410
2019 2516119 7701230 14084234 3440416 6468771
2020 499569 2516119 7701230 14084234 3440416
2021 NA NA NA NA NA
Reports for San Francisco Ballet
Variable: InvestmentEarningsOrLossesAmt
EIN: 941415298
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 4939 2881 4702 15692 77524
2016 12438 4939 2881 4702 15692
2017 11847575 -1836699 1138578 14271657 15677314
2018 6198570 11847575 -1836699 1138578 14271657
2019 6070692 6198570 11847575 -1836699 1138578
2020 457454 6070692 6198570 11847575 -1836699
2021 NA NA NA NA NA
Reports for San Francisco Ballet
Variable: OtherExpendituresAmt
EIN: 941415298
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 4939 2881 4702 1050593 2000258
2016 12438 4939 2881 4702 1050593
2017 7102876 5808274 5503775 5817478 4754895
2018 7932235 7102876 5808274 5503775 5817478
2019 8334967 7932235 7102876 5808274 5503775
2020 9340468 8334967 7932235 7102876 5808274
2021 NA NA NA NA NA
Reports for San Francisco Ballet
Variable: EndYearBalanceAmt
EIN: 941415298
fiscal_year CY CYM1 CYM2 CYM3 CYM4
2014 NA NA NA NA NA
2015 174 174 174 174 1035814
2016 174 174 174 174 174
2017 119047942 100219009 107033575 105867946 92513161
2018 125015507 119047942 100219009 107033575 105867946
2019 124452193 125015507 119047942 100219009 107033575
2020 113923812 124452193 125015507 119047942 100219009
2021 NA NA NA NA NA

Schedule O Information

# extract Schedule O for all variables
source("GET_VARS.R")
files <- dir(here("ballet_990_released_20230208"),
             full.names=TRUE)

schedule_o <- map_df(files,
                  ~get_df(filename =.x, schedule = 'o')) 

schedule_o <- schedule_o %>%
  filter_ein() %>%
  mutate(fiscal_year = as.numeric(as.character(fiscal_year)))
# schedule o information for each year for San Francisco 

schedule_o_ein <- schedule_o %>%
  filter(EIN == ein) %>%
  arrange(fiscal_year) 

name <- companies_to_ein %>%
  filter(EIN == ein) %>%
  pull(organization_name)

# iterate over each fiscal year and print information from each Schedule O variable
walk(schedule_o_ein$fiscal_year, ~
       {
         for_year <- schedule_o_ein %>%
           filter(fiscal_year == .x)%>%
           select(-c(where(is.na)))
         
         print(paste0("<span style='font-size:160%;'><b>",
         "-------------------------------- Fiscal Year: ",
                      .x, 
         '--------------------------------</b></span><br><br>'))
         
         walk(colnames(for_year), ~ { 
           
        section <- paste0("<span style='font-size:130%'><b>",
                             .x,
                             ": </b></span><br>", 
                             for_year[.x ],
                             "<br><br><br>")
        
         print(section)
         }) # end inner walk
}) # end outer walk

[1] “——————————– Fiscal Year: 2015——————————–

” [1] “ReturnTs:
2016-05-13T19:24:41-07:00


” [1] “EIN:
941415298


” [1] “filename:
201621379349307587_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part III, Line 3Following an assessment of the Association’s education programs and staffing, in April 2015 the Center for Dance Education (CDE) and the San Francisco Ballet School were merged into a single department of Education and Training. Moving forward, the CDE nomenclature will be dropped in favor of referencing the Association’s educational offerings as just that-Education Programs. Additionally, Dance in School and Communities (DISC) is being used more broadly to encompass the hallmark residency program in the San Francisco Unified School District public schools as well as partnerships with community-based organizations such as the Boys & Girls Clubs of San Francisco and the like.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part VI, Section A, Line 2Trustees James Marver and Stephanie Marver have a family relationship.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term of each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation, and (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 11bThe Association provided a complete copy of this Form 990 to all members of its governing body with a redaction of donor names and addresses from Form 990, Schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted on the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relationships Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “fiscal_year:
2015


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section B, Line 15The Association’s process for determining the compensation of the Artistic Director, the Executive Director, and the CFO involved analysis of the compensation by the Assessment Committee. The Artistic Director and Executive Director have written employment contracts, the terms of which are approved by the Assessment Committee.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent six years available to the public via its website. Form 990, governing documents and conflict of interest policy is also available upon request.


” [1] “SupplementalInformationDetail[10]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($278,739), unrealized loss on interest rate swap of ($589,071), ($15,858) change in post-retirement benefit obligation and $2,737 change in discount.


” [1] “——————————– Fiscal Year: 2016——————————–

” [1] “ReturnTs:
2017-05-15T16:36:54-07:00


” [1] “EIN:
941415298


” [1] “filename:
201741359349312579_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part III, Line 1oldest professional ballet company and one of the three largest ballet companies in the United States. The mission of the Ballet is to share the joy of dance with its community and around the globe and to provide the highest caliber of dance training in its School. Led by Artistic Director and Principal Choreographer, Helgi Tomasson, SF Ballet is accompanied by its own orchestra and operates one of the country’s most prestigious schools of ballet. Today we build on our heritage by commissioning groundbreaking dance from today’s top choreographers, by uncovering new choreographic talent,and by staging modern classics and the works that make up the canon of classic ballet. Our approach defines ballet in the 21st century, and it makes San Francisco Ballet the essential place to see the most adventurous dance in America. Guided in its early years by American dance pioneers the brothers Lew, Willam, and Harold Christensen, San Francisco Ballet currently presents more than 100 performances annually, both locally and internationally.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part VI, Section A, Line 2Trustees James Marver and Stephanie Marver have a family relationship.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term of each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation, and (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 11bThe Association provided a complete copy of this Form 990 to all members of its governing body with a redaction of donor names and addresses from Form 990, Schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted on the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relationships Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “fiscal_year:
2016


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section B, Line 15The Association’s process for determining the compensation of the Artistic Director, the Executive Director, and the CFO involved analysis of the compensation by the Assessment Committee. The Artistic Director and Executive Director have written employment contracts, the terms of which are approved by the Assessment Committee.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent seven years available to the public via its website. Form 990, governing documents and conflict of interest policy is also available upon request.


” [1] “SupplementalInformationDetail[10]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($49,325), unrealized loss on interest rate swap of ($2,617,653), ($135,668) change in post-retirement benefit obligation, $124,633 in capitalized expenses and ($43,416) change in discount.


” [1] “——————————– Fiscal Year: 2017——————————–

” [1] “ReturnTs:
2018-05-14T14:40:15-07:00


” [1] “EIN:
941415298


” [1] “filename:
201841349349305674_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part III, Line 1San Francisco Ballet is America’s oldest professional ballet company and one of the three largest ballet companies in the United States. The mission of the Ballet is to share the joy of dance with its community and around the globe and to provide the highest caliber of dance training in its School. Led by Artistic Director and Principal Choreographer, Helgi Tomasson, SF Ballet is accompanied by its own orchestra and operates one of the country’s most prestigious schools of ballet. Today we build on our heritage by commissioning groundbreaking dance from today’s top choreographers, by uncovering new choreographic talent, and by staging modern classics and the works that make up the canon of classic ballet. Our approach defines ballet in the 21st century, and it makes San Francisco Ballet the essential place to see the most adventurous dance in America. Guided in its early years by American dance pioneers the brothers Lew, Willam, and Harold Christensen, San Francisco Ballet currently presents more than 100 performances annually, both locally and internationally.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term for each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation, and (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section B, Line 11bThe Association provided a complete copy of this Form 990 to all members of its governing body with a redaction of donor names and addresses from Form 990, Schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted on the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relationships Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 15The Association’s process for determining the compensation of the Artistic Director, the Executive Director, and the CFO involve analysis of the compensation by the Assessment Committee. The Artistic Director and Executive Director have written employment contracts, the terms of which are approved by the Assessment Committee. Compensation for members of the Executive Team (key employees) is reviewed by the Executive Director.


” [1] “fiscal_year:
2017


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent seven years available to the public via its website. Form 990, governing documents and conflict of interest policy are also available upon request.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($178,674), unrealized gain on interest rate swap of $2,621,071, ($31,499) change in post-retirement benefit obligation and $87,857 change in discount.


” [1] “——————————– Fiscal Year: 2018——————————–

” [1] “ReturnTs:
2019-04-19T13:35:58-07:00


” [1] “EIN:
941415298


” [1] “filename:
201931099349300023_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part I, Line 1We seek to enhance our position as one of the world’s finest dance companies through our vitality, innovation, and diversity, and through our uncompromising commitment to artistic excellence based in the classical ballet tradition.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part III, Line 1San Francisco Ballet is America’s oldest professional ballet company and one of the three largest ballet companies in the United States. The mission of the Ballet is to share the joy of dance with its community and around the globe and to provide the highest caliber of dance training in its School. Led by Artistic Director and Principal Choreographer, Helgi Tomasson, SF Ballet is accompanied by its own orchestra and operates one of the country’s most prestigious schools of ballet. Today we build on our heritage of commissioning groundbreaking dance from today’s top choreographers, by uncovering new choreographic talent, and by staging modern classics and the works that make up the canon of classic ballet. Our approach defines ballet in the 21st century and it makes San Francisco Ballet the essential place to see the most adventurous dance in America. Guided in its early years by American dance pioneers the brothers Lew, Willam, and Harold Christensen, San Francisco Ballet currently presents more than 100 performances annually, both locally and internationally.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term for each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation and, (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 11bThe Form 990 was prepared based on audited financial statements by the organization’s finance and accounting staff, which was then reviewed by Grant Thornton, LLP. The Association provided a complete copy of this form to all members of its governing body with a redaction of donor names and addresses from Form 990, schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted to the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to all employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relationships Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “fiscal_year:
2018


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section B, Line 15Compensation and benefits for the Association’s Executive Director and CFO are negotiated with the Association’s Assessment Committee on an annual basis. Performance is reviewed on an annual basis by the Assessment Committee and documented via meeting minutes by the Board Assistant Secretary with recommendations given to the Board. Reviews occur (1) initially upon the hiring, (2) whenever the term of employment, if any, of such officer is renewed or extended, and (3) whenever such officer’s compensation is modified; provided, however, that separate review and approval pursuant to clause (3) shall not be required if a modification of compensation extends to substantially all employees of the Association. When reviewing compensation, the Assessment Committee also uses external comparisons for similar positions at similar organizations of similar size that are available online. Compensation and benefits for key employees are determined largely on whether they are union or non-union employees. For union employees, compensation and benefits are based on the collective bargaining agreements which are regularly renegotiated every three to five years (depending on the union). For non-union employees, compensation and benefits are negotiated on an individual basis upon initial hire with annual performance reviews thereafter documented with the employee’s direct supervisor using the organization’s standard forms on an annual/regular basis. Compensation for non-union employees are reviewed using external comparisons for similar positions at similar organizations of similar size that are available online.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent nine years available to the public via its website. Form 990, governing documents and conflict of interest policy are also available upon request.


” [1] “SupplementalInformationDetail[10]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($265,694), unrealized gain on interest rate swap of $1,390,892, $35,553 change in post-retirement benefit obligation and ($309,333) change in discount.


” [1] “——————————– Fiscal Year: 2019——————————–

” [1] “ReturnTs:
2020-04-01T20:29:09-07:00


” [1] “EIN:
941415298


” [1] “filename:
202020939349300802_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part I, Line 1We seek to enhance our position as one of the world’s finest dance companies through our vitality, innovation, and diversity, and through our uncompromising commitment to artistic excellence based in the classical ballet tradition.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part III, Line 1San Francisco Ballet is America’s oldest professional ballet company and one of the three largest ballet companies in the United States. The mission of the Ballet is to share the joy of dance with its community and around the globe and to provide the highest caliber of dance training in its School. Led by Artistic Director and Principal Choreographer, Helgi Tomasson, SF Ballet is accompanied by its own orchestra and operates one of the country’s most prestigious schools of ballet. Today we build on our heritage of commissioning groundbreaking dance from today’s top choreographers, by uncovering new choreographic talent, and by staging modern classics and the works that make up the canon of classic ballet. Our approach defines ballet in the 21st century and it makes San Francisco Ballet the essential place to see the most adventurous dance in America. Guided in its early years by American dance pioneers the brothers Lew, Willam, and Harold Christensen, San Francisco Ballet currently presents more than 100 performances annually, both locally and internationally.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term for each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation and, (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 11bThe Form 990 was prepared based on audited financial statements by the organization’s finance and accounting staff, which was then reviewed by Grant Thornton, LLP. The Association provided a complete copy of this form to all members of its governing body with a redaction of donor names and addresses from Form 990, schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted to the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to all employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relations Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “fiscal_year:
2019


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section B, Line 15Compensation and benefits for the Association’s Executive Director and CFO are negotiated with the Association’s Assessment Committee on an annual basis. Performance is reviewed on an annual basis by the Assessment Committee and documented via meeting minutes by the Board Assistant Secretary with recommendations given to the Board. Reviews occur (1) initially upon the hiring, (2) whenever the term of employment, if any, of such officer is renewed or extended, and (3) whenever such officer’s compensation is modified; provided, however, that separate review and approval pursuant to clause (3) shall not be required if a modification of compensation extends to substantially all employees of the Association. When reviewing compensation, the Assessment Committee also uses external comparisons for similar positions at similar organizations of similar size that are available online. Compensation and benefits for key employees are determined largely on whether they are union or non-union employees. For union employees, compensation and benefits are based on the collective bargaining agreements which are regularly renegotiated every three to five years (depending on the union). For non-union employees, compensation and benefits are negotiated on an individual basis upon initial hire with annual performance reviews thereafter documented with the employee’s direct supervisor using the organization’s standard forms on an annual/regular basis. Compensation for non-union employees are reviewed using external comparisons for similar positions at similar organizations of similar size that are available online.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent ten years available to the public via its website. Form 990, governing documents and conflict of interest policy are also available upon request.


” [1] “SupplementalInformationDetail[10]:
Form 990, Part X, Line 27A prior period adjustment between Unrestricted net assets and Temporarily restricted net assets was recorded during the fiscal year in the amount of $1,869,945. The adjustment was the cumulative effect of change in accounting for fixed assets acquired with donor restricted funds in accordance with ASU 2016-14.


” [1] “SupplementalInformationDetail[11]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($131,194), unrealized loss on interest rate swap of ($1,518,665), ($56,477) of event expenses reported on the Endowment Foundation form 990, ($407,603) change in post-retirement benefit obligation and $97,467 change in discount.


” [1] “——————————– Fiscal Year: 2020——————————–

” [1] “ReturnTs:
2021-04-06T12:09:46-07:00


” [1] “EIN:
941415298


” [1] “filename:
202101049349300235_public.xml


” [1] “SupplementalInformationDetail[1]:
Form 990, Part I, Line 1We seek to enhance our position as one of the world’s finest dance companies through our vitality, innovation, and diversity, and through our uncompromising commitment to artistic excellence based in the classical ballet tradition.


” [1] “SupplementalInformationDetail[2]:
Form 990, Part III, Line 1San Francisco Ballet is America’s oldest professional ballet company and one of the three largest ballet companies in the United States. The mission of the Ballet is to share the joy of dance with its community and around the globe and to provide the highest caliber of dance training in its School. Led by Artistic Director and Principal Choreographer, Helgi Tomasson, SF Ballet is accompanied by its own orchestra and operates one of the country’s most prestigious schools of ballet. Today we build on our heritage of commissioning groundbreaking dance from today’s top choreographers, by uncovering new choreographic talent, and by staging modern classics and the works that make up the canon of classic ballet. Our approach defines ballet in the 21st century and it makes San Francisco Ballet the essential place to see the most adventurous dance in America. Guided in its early years by American dance pioneers the brothers Lew, Willam, and Harold Christensen, San Francisco Ballet currently presents more than 100 performances annually, both locally and internationally.


” [1] “SupplementalInformationDetail[3]:
Form 990, Part VI, Section A, Line 6Any natural person shall be eligible to be a member of the Association (a "Member") but no legal person which is not a natural person, such as a foundation, trust, corporation or partnership, shall be eligible to be a Member. A natural person may become a Member by making a minimum contribution to the Association, the amount of such minimum contribution to be determined from time to time by resolution of the Board. In the case of a contribution in property, the determination of the Board or a Committee of the Board, Subcommittee, Advisory Committee or other person to whom this responsibility is delegated by the Board, as to the value of the property for membership purposes shall be conclusive. The Board may in its discretion waive the minimum contribution in the case of a person who has made intangible contributions to the Association in the past. The term for each Member as a Member shall commence when the person makes the requisite contribution to the Association (or the contribution is waived) and shall continue for a period of 12 months thereafter, at the expiration of which period it shall expire.


” [1] “SupplementalInformationDetail[4]:
Form 990, Part VI, Section A, Line 7aTrustees of the Association are elected by the Members for a term of three years.


” [1] “SupplementalInformationDetail[5]:
Form 990, Part VI, Section A, Line 7bMembers shall have, in addition to any other rights which may be granted to them under these By Laws or by law, the right to vote (1) for the election of Trustees, (2) on a disposition of all or substantially all of the Association’s assets, (3) on a merger of the Association with another corporation, (4) on a dissolution of the Association, (5) on an amendment of the Articles of Incorporation and, (6) on an amendment of these By Laws (unless the By Law amendment is approved by the Board alone in accordance with the terms of the By Laws).


” [1] “SupplementalInformationDetail[6]:
Form 990, Part VI, Section B, Line 11bThe Form 990 was prepared based on audited financial statements by the organization’s finance and accounting staff, which was then reviewed by Grant Thornton, LLP. The Association provided a complete copy of this form to all members of its governing body with a redaction of donor names and addresses from Form 990, schedule B, at the request of the donor. The Form 990 was reviewed and approved at a meeting of the Audit Committee. Subsequent to that review, the Form 990 was posted to the Association’s Trustee intranet website and Trustees were notified in writing of the availability of the Form 990 for their review.


” [1] “SupplementalInformationDetail[7]:
Form 990, Part VI, Section B, Line 12cQuestionnaires are issued annually to all employees, Trustees and others who have an annual reporting requirement under the policy. Responses are monitored and outstanding forms are followed up on until received. Upon receipt, the form is reviewed by the HR Manager (for employees) and the Board Relations Manager (for Trustees) for any known issues or relationships that need to be highlighted. Forms are further reviewed by the CFO. Matters requiring attention are reported to the Executive Director who may further report the matter to the Board Chair. Persons with a conflict are recused from discussions and do not vote on resolutions that pertain directly to their conflict.


” [1] “fiscal_year:
2020


” [1] “SupplementalInformationDetail[8]:
Form 990, Part VI, Section B, Line 15Compensation and benefits for the Association’s Executive Director and CFO are negotiated with the Association’s Assessment Committee on an annual basis. Performance is reviewed on an annual basis by the Assessment Committee and documented via meeting minutes by the Board Assistant Secretary with recommendations given to the Board. Reviews occur (1) initially upon the hiring, (2) whenever the term of employment, if any, of such officer is renewed or extended, and (3) whenever such officer’s compensation is modified; provided, however, that separate review and approval pursuant to clause (3) shall not be required if a modification of compensation extends to substantially all employees of the Association. When reviewing compensation, the Assessment Committee also uses external comparisons for similar positions at similar organizations of similar size that are available online. Compensation and benefits for key employees are determined largely on whether they are union or non-union employees. For union employees, compensation and benefits are based on the collective bargaining agreements which are regularly renegotiated every three to five years (depending on the union). For non-union employees, compensation and benefits are negotiated on an individual basis upon initial hire with annual performance reviews thereafter documented with the employee’s direct supervisor using the organization’s standard forms on an annual/regular basis. Compensation for non-union employees are reviewed using external comparisons for similar positions at similar organizations of similar size that are available online.


” [1] “SupplementalInformationDetail[9]:
Form 990, Part VI, Section C, Line 19Due to the age of the Organization, Form 1023 is currently unavailable for the general public and the Organization is exempt from the requirement to provide Form 1023, as it was filed prior to the July 15, 1987 requirement date. The Organization currently makes its audited financial statements for the most recent ten years available to the public via its website. From 990, governing documents and conflict of interest policy are also available upon request.


” [1] “SupplementalInformationDetail[10]:
Form 990, Part VIII, Line 2a - 2eOn March 6, 2020, by order of San Francisco Mayor London N. Breed, to prevent the spread of COVID-19, all public performances, events, and gatherings at the San Francisco War Memorial and Performing Arts Center were canceled. This order was followed by an announcement from the World Health Organization on March 11 where the WHO declared the novel strain of COVID-19 a global pandemic and recommended containment and mitigation measures worldwide. The pandemic has caused worldwide disruption to businesses and economic activity and has resulted in a significant drop in operating revenue for the Ballet. In compliance with local health mandates, the San Francisco Ballet has remained closed since March 16, 2020, and has not received revenue from admissions or onsite events since the closure of the War Memorial on March 6. The Ballet has been operating on a reduced expense budget but has increased its digital offerings and outreach to engage community members and donors. The Ballet continues to receive annual funding from individual and institutional donors and has been the recipient of new levels of support from individuals and institutions who have provided emergency funds for operations during the pandemic-related closure. The full extent of the adverse impact on the Ballet cannot be predicted at this time.


” [1] “SupplementalInformationDetail[11]:
Form 990, Part XI, Line 9Other changes in Net Assets consist of reversals of prior year grants of ($53,784), unrealized loss on interest rate swap of ($2,367,381), ($126,417) change in post-retirement benefit obligation and $155,193 change in discount.


# iterate through EINs where there was discordance and
# generate a table so we can better see what's going on

variable_name <- "BeginningYearBalanceAmt"

walk(1:length(companies_different), ~{
  name <- companies_to_ein %>%
    filter(EIN == companies_different[.x]) %>%
    pull(organization_name)
  
  table <- crossref %>% 
    rename_with(cols=everything(), ~gsub(variable_name, "", .)) %>%
    filter(EIN %in% companies_different[.x]) %>%
    select(-c(EIN, contains("difference"))) %>%
    make_table(title = paste0("Reports for ",
                              name, "<br>EIN: ", 
                              companies_different[.x],
                               ", Variable: ", variable_name))
  
  print(table)
  
#  print(table)

})